package dao;

import conn.ConData;
import model.Supplieinfomation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SupplieinfomationDao {
    Connection con=null;//连接数据库对象  默认值为空
    ConData conn=new ConData();//连接数据库驱动

    /**
     * 给supplieinfomation表添加数据
     * @param supplieinfomation 实体
     * @return
     */
    public int add(Supplieinfomation supplieinfomation){//参数类型 参数名  向方法里传参
        int num=0;//添加不成功
        //创建sql语句
        String sql="INSERT INTO supplieinfomation (suppliename,contacts,contactsphone,address,servicearea,legalpersonname,legalpersonphone,dateofestablishment,managestate,servicestate) VALUE(?,?,?,?,?,?,?,?,?,?)";
        //创建执行sql的对象A
        PreparedStatement ps;//带参数的执行sql语句对象
        try {
            //输出supplieinfomation的创建人方法的值
            System.out.println(supplieinfomation.getSuppliename());
            //调用连接数据库的方法
            con=conn.getCon();
            //返回一个PreparedStatement实例
            ps=con.prepareStatement(sql);

            ps.setString(1, supplieinfomation.getSuppliename());
            ps.setString(2, supplieinfomation.getContacts());
            ps.setString(3, supplieinfomation.getContactsphone());
            ps.setString(4, supplieinfomation.getAddress());
            ps.setString(5, supplieinfomation.getServicearea());
            ps.setString(6, supplieinfomation.getLegalpersonname());
            ps.setString(7, supplieinfomation.getLegalpersonphone());
            ps.setString(8, supplieinfomation.getDateofestablishment());
            ps.setString(9, supplieinfomation.getManagestate());
            ps.setString(10, supplieinfomation.getServicestate());
            num=ps.executeUpdate();//执行sql语句
            //关闭执行对象
            ps.close();
            con.close();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return num;
    }


    /**
     * 根据主键更新信息
     * @param supplieinfomation
     * @return
     */

    public int update(Supplieinfomation supplieinfomation){
        System.out.println("进入执行更新sql语句方法");
        int isSuccess=0;//修改未成功
        String sql="UPDATE supplieinfomation SET suppliename=? WHERE supplieinfomationid=?";//修改语句
        /*,contacts=?,contactsphone=?,address=?,servicearea=?,legalpersonname=?,legalpersonphone=?,managestate=?,servicestate=?*/
        PreparedStatement ps;
        try{
            //调用连接数据库的方法
            con=conn.getCon();
            //返回一个PrepareStatement实例
            ps=con.prepareStatement(sql);

            /*//时间格式转换
            java.util.Date dateofestablishment=supplieinfomation.getDateofestablishment();
            java.sql.Date Dateofestablishment=new java.sql.Date(dateofestablishment.getTime());*/

            ps.setString(1 , supplieinfomation.getSuppliename());
           /* ps.setString(2,supplieinfomation.getType());*/
           /* ps.setString(2,supplieinfomation.getContacts());
            ps.setString(3,supplieinfomation.getContactsphone());
            ps.setString(4,supplieinfomation.getAddress());
            ps.setString(5,supplieinfomation.getServicearea());
            ps.setString(6,supplieinfomation.getLegalpersonname());
            ps.setString(7,supplieinfomation.getLegalpersonphone());*/
            /*ps.setString(8,supplieinfomation.getDateofestablishment);*/
            /*ps.setString(8,supplieinfomation.getManagestate());
            ps.setString(9,supplieinfomation.getServicestate());*/
            ps.setInt(2,supplieinfomation.getSupplieinfomationid());



            //执行修改
            isSuccess=ps.executeUpdate();
            //关闭执行对象
            ps.close();
            con.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return isSuccess;
    }


    /*
     * 根据主键删除信息
     * */

    public int del(Supplieinfomation supplieinfomation){
        int isDel=0;//删除未成功
        //创建执行sql语句
        String sql="DELETE FROM supplieinfomation WHERE supplieinfomationid=?";
        //创建执行sql的对象
        PreparedStatement ps;
        try{
            con=conn.getCon();
            ps=con.prepareStatement(sql);
            //设置删除条件
            ps.setInt(1, supplieinfomation.getSupplieinfomationid());
            //删除
            isDel=ps.executeUpdate();
            //关闭执行对象
            ps.close();
            //关闭连接对象
            con.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
        return isDel;
    }



    /*
     * 根据查询条件返回查询结果
     *
     * */
    public List<Supplieinfomation> getAll(Supplieinfomation supplieinfomation){
        System.out.println("进入查询方法");
        List<Supplieinfomation> listSupplieinfomation=new ArrayList<Supplieinfomation>();
        //创建sql字符串
        String str="SELECT * FROM supplieinfomation ";
        int Supplieinfomationid=0;
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Supplieinfomation b=new Supplieinfomation();
                //给对象赋值
                b.setSupplieinfomationid(rs.getInt(1));
                b.setSuppliename(rs.getString(2));
                b.setType(rs.getString(3));
                b.setContacts(rs.getString(4));
                b.setContactsphone(rs.getString(5));
                b.setAddress(rs.getString(6));
                b.setServicearea(rs.getString(7));
                b.setLegalpersonname(rs.getString(8));
                b.setLegalpersonphone(rs.getString(9));
                b.setDateofestablishment(rs.getString(10));
                b.setManagestate(rs.getString(11));
                b.setServicestate(rs.getString(12));
                //将对象实体添加到集合中
                listSupplieinfomation.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSupplieinfomation);
        return listSupplieinfomation;
    }


    public List<Supplieinfomation> getDan(Supplieinfomation supplieinfomation){
        System.out.println("进入单项查询方法");
        List<Supplieinfomation> listSupplieinfomation=new ArrayList<Supplieinfomation>();
        //创建sql字符串
        String str="SELECT * FROM supplieinfomation WHERE suppliename=?";
        int Supplieinfomationid=0;
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //设置查询条件
            ps.setString(1,supplieinfomation.getSuppliename());
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Supplieinfomation b=new Supplieinfomation();
                //给对象赋值
                b.setSupplieinfomationid(rs.getInt(1));
                b.setSuppliename(rs.getString(2));
                b.setType(rs.getString(3));
                b.setContacts(rs.getString(4));
                b.setContactsphone(rs.getString(5));
                b.setAddress(rs.getString(6));
                b.setServicearea(rs.getString(7));
                b.setLegalpersonname(rs.getString(8));
                b.setLegalpersonphone(rs.getString(9));
                b.setDateofestablishment(rs.getString(10));
                b.setManagestate(rs.getString(11));
                b.setServicestate(rs.getString(12));
                //将对象实体添加到集合中
                listSupplieinfomation.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSupplieinfomation);
        return listSupplieinfomation;
    }


    public List<Supplieinfomation> getAddc(Supplieinfomation supplieinfomation){
        System.out.println("进入更改中使用id查询方法");
        List<Supplieinfomation> listSupplieinfomation=new ArrayList<Supplieinfomation>();
        //创建sql字符串
        String str="SELECT * FROM supplieinfomation WHERE supplieinfomationid=?";
        con=conn.getCon();
        try{
            //创建执行sql的对象
            PreparedStatement ps=con.prepareStatement(str);
            System.out.println("查询语句:"+str);
            //设置查询条件
            ps.setInt(1,supplieinfomation.getSupplieinfomationid());
            //返回查询记录结果的记录集
            ResultSet rs=ps.executeQuery();

            //将记录集存入集合当中
            while(rs.next()){
                //声明一个实体对象
                Supplieinfomation b=new Supplieinfomation();
                //给对象赋值
                b.setSupplieinfomationid(rs.getInt(1));
                b.setSuppliename(rs.getString(2));
                b.setType(rs.getString(3));
                b.setContacts(rs.getString(4));
                b.setContactsphone(rs.getString(5));
                b.setAddress(rs.getString(6));
                b.setServicearea(rs.getString(7));
                b.setLegalpersonname(rs.getString(8));
                b.setLegalpersonphone(rs.getString(9));
                b.setDateofestablishment(rs.getString(10));
                b.setManagestate(rs.getString(11));
                b.setServicestate(rs.getString(12));
                //将对象实体添加到集合中
                listSupplieinfomation.add(b);

            }
            //关闭记录集
            rs.close();
            //关闭执行的sql对象
            ps.close();
            //关闭连接
            con.close();

        }catch(SQLException e){
            e.printStackTrace();
        }
        System.out.println(listSupplieinfomation);
        return listSupplieinfomation;
    }
}
